﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;


public static class DB
{
    public static SQLiteConnection m_dbConnection;
    public static List<int> anos;
    public static List<double> values;

    public static void Program()
    {
        createNewDatabase();
        connectToDatabase();
        createTable();

        foreach (Data dt in ReadExel.list)
            fillTable(dt);


    }

    // Creates an empty database file
    public static void createNewDatabase()
    {
        SQLiteConnection.CreateFile("IPC_POrtugal_1997_2013.sqlite");
    }

    // Creates a connection with our database file.
    public static void connectToDatabase()
    {
        m_dbConnection = new SQLiteConnection("Data Source=IPC_POrtugal_1997_2013.sqlite;Version=3;");
        m_dbConnection.Open();
    }

    // Creates a table named  dados
    public static void createTable()
    {
        string sql = "create table if not exists dados (Ano int, IPC float, VarAn float, rMinM float, rMaxM float, PIB float, RendNac float, RendDisp float, Renume float)";
        SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
        command.ExecuteNonQuery();
        m_dbConnection.Close();
    }

    
    public static void fillTable(Data dt)
    {
        string sql;


        m_dbConnection.Open();
        sql = "insert into dados (Ano, IPC, VarAn, rMinM , rMaxM , PIB , RendNac , RendDisp , Renume ) values (" + dt.ano + "," + dt.ipc + ","
        + dt.varAno + "," + dt.rMinM + "," + dt.rMaxM + "," + dt.pib + "," + dt.rendNac + "," + dt.rendDisp + "," + dt.renume + ")";

        SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
        command.ExecuteNonQuery();
        m_dbConnection.Close();

    }
  

    public static void getGraph(string value , string anoI , string anoF)
    {
       

        m_dbConnection.Open();


        
        string sql = "select * from dados Where Ano BETWEEN " + anoI + " and " + anoF;       
        SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
        SQLiteDataReader reader = command.ExecuteReader();
        anos = new List<int>();
        values = new List<double>();
        while (reader.Read())
        {
           
            
            try
            {
                double valores = Convert.ToDouble(reader[value]);
                int ano = Convert.ToInt16(reader["Ano"]);
                values.Add(valores);
                anos.Add(ano);
            }
            catch
            {

            }
                     
        }
      


        m_dbConnection.Close();
    }
    
      
}

